package com.keda.dao;

import com.keda.model.Product;
import com.keda.model.ProductDetail;
import com.keda.util.DBUtil;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.dbutils.handlers.columns.IntegerColumnHandler;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class ProductDao {

    private static final Integer PAGE_SIZE = 8;

    public List<Product> querySpecialProducts() {
        String sql = "SELECT id, name, price, proPic FROM t_product" +
                " WHERE specialPrice = 1 order by specialPriceTime desc limit 8";
        List<Product> products = DBUtil.executeQuery(sql, Product.class);
        return products;
    }

    public List<Product> queryHotProducts() {

        String sql = "SELECT id, name, price, proPic FROM t_product " +
                " WHERE hot = 1 order by hotTime desc limit 6";
        List<Product> products = DBUtil.executeQuery(sql, Product.class);
        return products;
    }


    public Integer count(String bigTypeId, String smallTypeId, String keyword) {

        Connection connection = DBUtil.createConnection();

        String sql = "SELECT count(*) FROM t_product t where 1 = 1";
        if (bigTypeId != null && !bigTypeId.isEmpty()) {
            sql += " and bigTypeId = " + bigTypeId;
        }
        if (smallTypeId != null && !smallTypeId.isEmpty()) {
            sql += " and smallTypeId = " + smallTypeId;
        }
        if(keyword != null && !keyword.trim().isEmpty()) {
            sql += " and (name like '%" + keyword.trim() + "%' or description like '%" + keyword.trim() + "%')";
        }

        System.out.println(String.format("统计商品的SQL： %s", sql));

        QueryRunner queryRunner = new QueryRunner();
        Long totalCount = null;
        try {
            totalCount = queryRunner.query(connection, sql, new ScalarHandler<>());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (totalCount == null) {
            return 0;
        }
        return totalCount.intValue();


    }

    public List<Product> query(String bigTypeId, String smallTypeId,
                               String keyword, String page) {

        Connection connection = DBUtil.createConnection();

        String sql = "SELECT id, name, proPic, price FROM t_product t where 1 = 1";
        if (bigTypeId != null && !bigTypeId.isEmpty()) {
            sql += " and bigTypeId = " + bigTypeId;
        }
        if (smallTypeId != null && !smallTypeId.isEmpty()) {
            sql += " and smallTypeId = " + smallTypeId;
        }
        if(keyword != null && !keyword.trim().isEmpty()) {
            sql += " and (name like '%" + keyword.trim() + "%' or description like '%" + keyword.trim() + "%')";
        }

        // 构建分页
        int pageNum = 1;
        try {
            pageNum = Integer.parseInt(page);
        } catch (Exception e) {

        }
        if (pageNum == 0) {
            pageNum = 1;
        }

        sql += " limit " + (pageNum - 1) * PAGE_SIZE + ", " + PAGE_SIZE;

//        sql += " limit 8";

        System.out.println(String.format("查询商品的SQL： %s", sql));

        QueryRunner queryRunner = new QueryRunner();
        List<Product> products = null;
        try {
            products = queryRunner.query(connection, sql, new BeanListHandler<Product>(Product.class));
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return products;
    }

    public ProductDetail queryById(int productId) {
        Connection connection = DBUtil.createConnection();

        String sql = "SELECT id, name, proPic, price, description, stock," +
                " hot, hotTime, specialPrice, specialPriceTime, bigTypeId, smallTypeId " +
                "FROM t_product t where id = ?";
        QueryRunner queryRunner = new QueryRunner();
        ProductDetail product = null;
        try {
            product = queryRunner.query(connection, sql,
                    new BeanHandler<>(ProductDetail.class), productId);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return product;
    }
}
